import datetime
import re
import time
from collections import Counter
import xlwings as xw
from Utils.MyJMP import JMP, os, pd
from Utils.SomeTools import MailItem

__version__ = 'V01'
# pd.set_option('display.max_columns', None)
# pd.set_option('display.max_rows', None)
# pd.set_option('display.width', None)
os.chdir(os.path.dirname(__file__))


class CriticalReport(JMP):
    def __init__(self):
        super(CriticalReport, self).__init__()
        path = [r'.\Data\Daily\RawData', 'Template.xlsx', r'.\Output']
        self.raw_data_path, self.template, self.output_path = path
        self.output_name = fr'{os.path.abspath(self.output_path)}\
        \MM-{str(datetime.date.today()).replace("-", "")[-4:]}\
        -Shasta Critical Stations Report.xlsx'
        self.qty_df, self.info_df = None, None

    def get_file(self, key_name):
        file = [os.path.join(self.raw_data_path, x) for x in os.listdir(self.raw_data_path) if key_name in x]
        if not file:
            return
        else:
            return file[0]

    def get_qty_data(self):
        self.qty_df = self.get_file('Stratification')
        if not self.qty_df:
            return pd.DataFrame()
        cols_needed = ['Station Name', 'Line', 'Test Count', 'Fail', 'Retest Count']
        df = pd.read_csv(self.qty_df, header=1, usecols=cols_needed)
        df['Test Count'] = df['Test Count'].apply(lambda x: str(x).replace(',', '')).astype('int32')
        df = df.sort_values(by='Test Count').drop_duplicates(subset=['Station Name', 'Line'], keep='last')
        return df

    def get_info_data(self):
        self.info_df = self.get_file('UnitTest')
        if not self.info_df:
            return pd.DataFrame()
        cols_needed = ['Product Code', 'Station Name', 'Test Result', 'Station ID', 'Fail Message', 'Test']
        df = pd.read_csv(self.info_df, usecols=cols_needed)
        df['Count'] = 1
        df['Line'] = df['Station ID'].apply(lambda x: str(x).split('_')[1] if '_' in x else x)
        df = df[~((df['Station Name'] == 'RSF-PNP') & (df['Product Code'] == 'D38'))]
        df = df.query(~('Station' == 'RSF-PNP' & 'Prudect Code' == 'D38'))
        return df

    @staticmethod
    def _fill_qty(station_pos, qty_df: pd.DataFrame, sht, pos, key_name, y, qty_col, offset):
        """
        :param station_pos: 站位所在行
        :param qty_df: insight投产/复测/不良的表格对象
        :param sht: 报告模板
        :param pos: 字典，键：每个Line所在的列数 值：Line
        :param key_name: 模板里投产/复测/不良的关键词
        :param y: 模板里的站位名称
        :param qty_col: qty_df里面的数量列名
        :param offset: 投产/重测/不良的位置相对站位行的补偿距离
        :return:
        """
        station_pos += offset
        if str(sht.range(f'D{station_pos}').value).lower().startswith(key_name):
            for k, v in pos.items():  # k：位置， v：Line
                cell = sht.cells(station_pos, k)
                if v not in qty_df['Line'].values:
                    # cell.value, cell.color = '无此Line', (255, 0, 0)
                    pass
                else:
                    qty = qty_df[(qty_df['Station Name'] == y) & (qty_df['Line'] == v)]
                    if len(qty):
                        cell.value = qty[qty_col].values
                    # error_info = '站位和线体不匹配'
                    # value = qty[qty_col].values if len(qty) else error_info
                    # cell.value = value
                    # if str(value) == error_info:
                    #     cell.color = (255, 0, 0)

    @staticmethod
    def _fill_info(station_pos, info_df: pd.DataFrame, sht, pos, _type: str, y, offset):
        station_pos += offset
        if str(sht.range(f'D{station_pos}').value).lower().startswith(_type.lower()):
            for k, v in pos.items():  # k：位置， v：Line
                cell = sht.cells(station_pos, k)
                if v not in info_df['Line'].values:
                    pass
                else:
                    info = info_df[(info_df['Station Name'] == y) & (info_df['Line'] == v) & (
                            info_df['Test Result'] == _type)]
                    if len(info):
                        info_data = sorted(dict(Counter(info['Test'])).items(), key=lambda d: d[1],
                                           reverse=True)
                        retest_or_fail_info = '\n'.join([fr'{_[0]}*{str(_[1])}' for _ in info_data])
                        cell.api.AddComment(retest_or_fail_info)

    def output_report(self):
        start = time.time()
        qty_df = self.get_qty_data()
        info_df = self.get_info_data()
        app = xw.App(visible=False, add_book=False)
        wb = app.books.open(self.template)
        delete_rows = []
        for i in wb.sheets:
            if 'YR' == i.name.strip():
                sht = wb.sheets[i.name]
                rows, columns = sht.used_range.shape  # 列，行
                for num, val in enumerate(sht.range(f'B1:B{rows}').value):  # 遍历B列 抓取 Insight name关键词
                    pos = {}
                    if str(val).upper().startswith('INSIGHT'):  # 如果满足
                        data = sht.range((num + 1, 1), (num + 1, columns)).value  # 获取当前行的所有数据
                        print(fr'第{num + 1}行', data)
                        if str(data[0]).lower() == 'y':
                            delete_rows.append(num + 1)
                        for n, v in enumerate(data):  # 定位Line，获取所在列的数字
                            if v:
                                value = re.findall(r'\((B.*?)\)', v)  # 楼层都是以B开始，取B
                                if value:
                                    pos[n + 1] = value[0]
                        for x, y in enumerate(sht.range(f'B{num + 2}:B{rows}').value):
                            station_pos = x + num + 2  # 每个站位的所在行
                            if str(y).upper().startswith('INSIGHT'):  # 如果遍历到下一次Insight Name，直接退出此Insight Name下的循环
                                break
                            if str(y) in qty_df['Station Name'].values:
                                if len(qty_df):  # 投产数量信息
                                    key_dic = {'input': 'Test Count', 'fail': 'Fail', 'retest': 'Retest Count'}
                                    for n, v in enumerate(key_dic.keys()):
                                        self._fill_qty(*[station_pos, qty_df, sht, pos, v, y, key_dic[v], n])  # 数量信息
                                if len(info_df):  # FAIL/RETEST的备注信息
                                    info_dic = {'fail': 'FAIL', 'retest': 'RETEST'}
                                    for n, v in enumerate(info_dic.keys()):
                                        self._fill_info(
                                            *[station_pos, info_df, sht, pos, info_dic[v], y, n + 1])  # 添加注解
                for row in delete_rows.__reversed__():
                    sht[f'{row}:{row}'].delete()
                for c in sht.api.Comments:
                    obj = c.Shape.OLEFormat.Object.Font
                    obj.Size = 10
                    obj.Name = 'Calibri'
                    c.Shape.Width = 250
                    c.Shape.Height = 229
        wb.save(self.output_name)
        wb.close()
        app.quit()
        return fr'{round(time.time() - start, 3)}秒'


report = CriticalReport()

if __name__ == '__main__':
    report.output_report()
